/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package data;

import business.GpsRecord;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author stana
 */
public class TrainingTable
{

    private Connection conn;
    private Statement statement;
    
    public TrainingTable()
    { 
        conn=data.Connection.getInstance().getConnection();
        statement=data.Connection.getInstance().getStatement();
    }



    public String getDescription(Date date)
    {
        Format formater = new SimpleDateFormat("yyyy-MM-dd");
        String dateS = formater.format(date);
        String ret = "";
        try
        {

            ResultSet rs = statement.executeQuery("select description from training where date='" + dateS + "';");
            while (rs.next())
            {
                ret += rs.getString("description");
            }
            rs.close();
        } catch (SQLException ex)
        {
            Logger.getLogger(TrainingTable.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ret;
    }

    public void insertData(Date date, String description)
    {
        try
        {
            Format formater = new SimpleDateFormat("yyyy-MM-dd");
            String dateS = formater.format(date);
            int id = getLastIndex();
            id++;
            statement.execute("insert into training values(" + String.valueOf(id) + ",'"
                    + dateS + "','" + description + "');");
        } catch (SQLException ex)
        {
            Logger.getLogger(TrainingTable.class.getName()).log(Level.SEVERE, null, ex);
        }


    }

    public void setDescription(Date date, String description) throws SQLException
    {
        
        if ((description != null && !description.equals(""))||trainingExist(date))
        {
            Format formater = new SimpleDateFormat("yyyy-MM-dd");
            String dateS = formater.format(date);
            if (trainingExist(date))
            {
                statement.execute("update training set description='" + description
                        + "' where date='" + dateS + "';");
            } else
            {
                int id = getLastIndex();
                id++;

                statement.execute("insert into training values(" + String.valueOf(id) + ",'"
                        + dateS + "','" + description + "');");
            }
        }

    }

    private boolean trainingExist(Date date)
    {
        if (date==null)
        {
            return false;
        }
        Format formater = new SimpleDateFormat("yyyy-MM-dd");
        String dateS = formater.format(date);
        try
        {
            ResultSet rs = statement.executeQuery("select * from training where date='" + dateS + "';");
            if (rs.next())
            {
                return true;
            } else
            {
                return false;
            }
        } catch (SQLException ex)
        {
            Logger.getLogger(TrainingTable.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    private int getLastIndex()
    {
        try
        {
            ResultSet rs = statement.executeQuery("select max(id) from training;");
            while (rs.next())
            {
                return rs.getInt("max(id)");
            }

        } catch (SQLException ex)
        {
            Logger.getLogger(TrainingTable.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }

    public int getIndex(Date date)
    {
        Format formater = new SimpleDateFormat("yyyy-MM-dd");
        String dateS = formater.format(date);
        try
        {
            ResultSet rs = statement.executeQuery("select id from training where date='" + dateS + "';");
            if (rs.next())
            {
                return rs.getInt("id");
            } else
            {
                return 0;
            }
        } catch (SQLException ex)
        {
            Logger.getLogger(TrainingTable.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }
    
    /**
     * 
     * @param date
     * @return vrati true pokud k uvedenemu datu existuje zaznam v gps_record
     */
    public boolean haveRecord(Date date)
    {
        Format formater = new SimpleDateFormat("yyyy-MM-dd");
        String dateS = formater.format(date);
        try
        {
            ResultSet rs = statement.executeQuery(" select training.id from "
                    + "TRAINING,GPS_RECORD where gps_record.training_id=TRAINING.id"
                    + " and TRAINING.date='"+dateS+"';");
            if (rs.next())
            {
                return true;
            } else
            {
                return false;
            }
        } catch (SQLException ex)
        {
            Logger.getLogger(TrainingTable.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    
    public List getGPSRecord(Date date)
    {
        List<GpsRecord> list=new LinkedList<GpsRecord>();
        if(date!=null)
        {
        Format formater = new SimpleDateFormat("yyyy-MM-dd");
        String dateS = formater.format(date);
        
        GpsRecord record;
        try
        {
            ResultSet rs = statement.executeQuery(" select  GPS_RECORD.* from "
                    + "TRAINING,GPS_RECORD where gps_record.training_id=TRAINING.id"
                    + " and TRAINING.date='"+dateS+"';");
            while(rs.next())
            {
                record=new GpsRecord(rs.getDouble("latitude"),
                        rs.getDouble("longitude"),
                        rs.getDouble("altitude"),
                        rs.getDouble("speed"),
                        rs.getString("time")
                        );
                list.add(record);
            }
        } catch (SQLException ex)
        {
            Logger.getLogger(TrainingTable.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
        }
        return list;
        
        
    }
}
